
df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
df=sqlContext.sql("""create table bitcoin.all as select * from base where
                 smslabel like '%币%'
                 """)


df=sqlContext.sql("""create table bitcoin.huobiwang_all as select * from bitcoin.all where
                 smslabel = '火币网'
                 """)

df=sqlContext.sql("""create table bitcoin.recent as select * from bitcoin.all ORDER BY insert_time DESC limit 300000
                 """)

df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
df=sqlContext.sql("""create table dw.tmp_20101114 as select * from base where pt='201805' AND
                    (msg_content like '%足额%'
                    OR msg_content like '%总金额%'
                    OR msg_content like '%总额%'
                    OR msg_content like '%资金%'
                    OR msg_content like '%追回%'
                    OR msg_content like '%转账%'
                    OR msg_content like '%注册%'
                    OR msg_content like '%仲裁%'
                    OR msg_content like '%滞纳金%'
                    OR msg_content like '%支付%'
                    OR msg_content like '%征信%'
                    OR msg_content like '%账款%'
                    OR msg_content like '%账户%'
                    OR msg_content like '%账号%'
                    OR msg_content like '%账单%'
                    OR msg_content like '%账%'
                    OR msg_content like '%帐户%'
                    OR msg_content like '%诈骗%'
                    OR msg_content like '%月%'
                    OR msg_content like '%元%'
                    OR msg_content like '%逾期%'
                    OR msg_content like '%余额%'
                    OR msg_content like '%用钱%'
                    OR msg_content like '%用款%'
                    OR msg_content like '%银行卡%'
                    OR msg_content like '%银行贷款%'
                    OR msg_content like '%银行%'
                    OR msg_content like '%易贷%'
                    OR msg_content like '%以免%'
                    OR msg_content like '%一笔%'
                    OR msg_content like '%业务%'
                    OR msg_content like '%央行%'
                    OR msg_content like '%验证码%'
                    OR msg_content like '%需电核%'
                    OR msg_content like '%刑事%'
                    OR msg_content like '%刑法%'
                    OR msg_content like '%刑罚%'
                    OR msg_content like '%信誉%'
                    OR msg_content like '%信用卡%'
                    OR msg_content like '%信用等级%'
                    OR msg_content like '%信用%'
                    OR msg_content like '%信贷%'
                    OR msg_content like '%现金额%'
                    OR msg_content like '%现金%'
                    OR msg_content like '%下款%'
                    OR msg_content like '%我行%'
                    OR msg_content like '%尾款%'
                    OR msg_content like '%尾号%'
                    OR msg_content like '%违约%'
                    OR msg_content like '%网贷%'
                    OR msg_content like '%万元%'
                    OR msg_content like '%拖欠%'
                    OR msg_content like '%通过%'
                    OR msg_content like '%调解%'
                    OR msg_content like '%提现%'
                    OR msg_content like '%提取%'
                    OR msg_content like '%提钱花%'
                    OR msg_content like '%提额%'
                    OR msg_content like '%数额%'
                    OR msg_content like '%收款%'
                    OR msg_content like '%失信%'
                    OR msg_content like '%审批%'
                    OR msg_content like '%审核%'
                    OR msg_content like '%申请%'
                    OR msg_content like '%入账%'
                    OR msg_content like '%人民币%'
                    OR msg_content like '%全款%'
                    OR msg_content like '%全国%'
                    OR msg_content like '%全额%'
                    OR msg_content like '%取现%'
                    OR msg_content like '%清收%'
                    OR msg_content like '%清偿%'
                    OR msg_content like '%欠息%'
                    OR msg_content like '%欠款%'
                    OR msg_content like '%钱款%'
                    OR msg_content like '%钱包%'
                    OR msg_content like '%欺诈%'
                    OR msg_content like '%履约%'
                    OR msg_content like '%利息%'
                    OR msg_content like '%利率%'
                    OR msg_content like '%老赖%'
                    OR msg_content like '%来款%'
                    OR msg_content like '%款项%'
                    OR msg_content like '%扣款%'
                    OR msg_content like '%开通%'
                    OR msg_content like '%拒绝%'
                    OR msg_content like '%金融%'
                    OR msg_content like '%金额%'
                    OR msg_content like '%借款%'
                    OR msg_content like '%借到%'
                    OR msg_content like '%借贷%'
                    OR msg_content like '%结清%'
                    OR msg_content like '%激活%'
                    OR msg_content like '%汇款%'
                    OR msg_content like '%黑名单%'
                    OR msg_content like '%合同%'
                    OR msg_content like '%还清%'
                    OR msg_content like '%还款%'
                    OR msg_content like '%归还%'
                    OR msg_content like '%个人信用%'
                    OR msg_content like '%付款%'
                    OR msg_content like '%风险%'
                    OR msg_content like '%分期%'
                    OR msg_content like '%分行%'
                    OR msg_content like '%费用%'
                    OR msg_content like '%费率%'
                    OR msg_content like '%放款%'
                    OR msg_content like '%法律手段%'
                    OR msg_content like '%罚息%'
                    OR msg_content like '%罚金%'
                    OR msg_content like '%额度%'
                    OR msg_content like '%到期日%'
                    OR msg_content like '%到期%'
                    OR msg_content like '%到款%'
                    OR msg_content like '%贷款%'
                    OR msg_content like '%贷将%'
                    OR msg_content like '%贷%'
                    OR msg_content like '%代扣%'
                    OR msg_content like '%大额%'
                    OR msg_content like '%打款%'
                    OR msg_content like '%存入%'
                    OR msg_content like '%催缴%'
                    OR msg_content like '%催告%'
                    OR msg_content like '%储蓄卡%'
                    OR msg_content like '%出款%'
                    OR msg_content like '%充值%'
                    OR msg_content like '%承担%'
                    OR msg_content like '%成功%'
                    OR msg_content like '%车贷%'
                    OR msg_content like '%超期%'
                    OR msg_content like '%偿清%'
                    OR msg_content like '%偿还%'
                    OR msg_content like '%财富%'
                    OR msg_content like '%避免%'
                    OR msg_content like '%本息%'
                    OR msg_content like '%本期%'
                    OR msg_content like '%本金%'
                    OR msg_content like '%被执行人%'
                    OR msg_content like '%保证%'
                    OR msg_content like '%绑定%'
                    OR msg_content like '%按时%'
                    OR msg_content like '%逾 期%'
                    OR msg_content like '%追偿%'
                    OR msg_content like '%到账%'
                    OR msg_content like '%绑卡%'
                    OR msg_content like '%应还%'
                    OR msg_content like '%申请已通过%'
                    OR msg_content like '%代还%'
                    OR msg_content like '%需还%'
                    OR msg_content like '%要还%'
                    OR msg_content like '%成功还%'
                    OR msg_content like '%已还%'
                    OR msg_content like '%要到期%')
                 """)


OR msg_content like '%足额%'
OR msg_content like '%总金额%'
OR msg_content like '%总额%'
OR msg_content like '%资金%'
OR msg_content like '%追回%'
OR msg_content like '%转账%'
OR msg_content like '%注册%'
OR msg_content like '%仲裁%'
OR msg_content like '%滞纳金%'
OR msg_content like '%支付%'
OR msg_content like '%征信%'
OR msg_content like '%账款%'
OR msg_content like '%账户%'
OR msg_content like '%账号%'
OR msg_content like '%账单%'
OR msg_content like '%账%'
OR msg_content like '%帐户%'
OR msg_content like '%诈骗%'
OR msg_content like '%月%'
OR msg_content like '%元%'
OR msg_content like '%逾期%'
OR msg_content like '%余额%'
OR msg_content like '%用钱%'
OR msg_content like '%用款%'
OR msg_content like '%银行卡%'
OR msg_content like '%银行贷款%'
OR msg_content like '%银行%'
OR msg_content like '%易贷%'
OR msg_content like '%以免%'
OR msg_content like '%一笔%'
OR msg_content like '%业务%'
OR msg_content like '%央行%'
OR msg_content like '%验证码%'
OR msg_content like '%需电核%'
OR msg_content like '%刑事%'
OR msg_content like '%刑法%'
OR msg_content like '%刑罚%'
OR msg_content like '%信誉%'
OR msg_content like '%信用卡%'
OR msg_content like '%信用等级%'
OR msg_content like '%信用%'
OR msg_content like '%信贷%'
OR msg_content like '%现金额%'
OR msg_content like '%现金%'
OR msg_content like '%下款%'
OR msg_content like '%我行%'
OR msg_content like '%尾款%'
OR msg_content like '%尾号%'
OR msg_content like '%违约%'
OR msg_content like '%网贷%'
OR msg_content like '%万元%'
OR msg_content like '%拖欠%'
OR msg_content like '%通过%'
OR msg_content like '%调解%'
OR msg_content like '%提现%'
OR msg_content like '%提取%'
OR msg_content like '%提钱花%'
OR msg_content like '%提额%'
OR msg_content like '%数额%'
OR msg_content like '%收款%'
OR msg_content like '%失信%'
OR msg_content like '%审批%'
OR msg_content like '%审核%'
OR msg_content like '%申请%'
OR msg_content like '%入账%'
OR msg_content like '%人民币%'
OR msg_content like '%全款%'
OR msg_content like '%全国%'
OR msg_content like '%全额%'
OR msg_content like '%取现%'
OR msg_content like '%清收%'
OR msg_content like '%清偿%'
OR msg_content like '%欠息%'
OR msg_content like '%欠款%'
OR msg_content like '%钱款%'
OR msg_content like '%钱包%'
OR msg_content like '%欺诈%'
OR msg_content like '%履约%'
OR msg_content like '%利息%'
OR msg_content like '%利率%'
OR msg_content like '%老赖%'
OR msg_content like '%来款%'
OR msg_content like '%款项%'
OR msg_content like '%扣款%'
OR msg_content like '%开通%'
OR msg_content like '%拒绝%'
OR msg_content like '%金融%'
OR msg_content like '%金额%'
OR msg_content like '%借款%'
OR msg_content like '%借到%'
OR msg_content like '%借贷%'
OR msg_content like '%结清%'
OR msg_content like '%激活%'
OR msg_content like '%汇款%'
OR msg_content like '%黑名单%'
OR msg_content like '%合同%'
OR msg_content like '%还清%'
OR msg_content like '%还款%'
OR msg_content like '%归还%'
OR msg_content like '%个人信用%'
OR msg_content like '%付款%'
OR msg_content like '%风险%'
OR msg_content like '%分期%'
OR msg_content like '%分行%'
OR msg_content like '%费用%'
OR msg_content like '%费率%'
OR msg_content like '%放款%'
OR msg_content like '%法律手段%'
OR msg_content like '%罚息%'
OR msg_content like '%罚金%'
OR msg_content like '%额度%'
OR msg_content like '%到期日%'
OR msg_content like '%到期%'
OR msg_content like '%到款%'
OR msg_content like '%贷款%'
OR msg_content like '%贷将%'
OR msg_content like '%贷%'
OR msg_content like '%代扣%'
OR msg_content like '%大额%'
OR msg_content like '%打款%'
OR msg_content like '%存入%'
OR msg_content like '%催缴%'
OR msg_content like '%催告%'
OR msg_content like '%储蓄卡%'
OR msg_content like '%出款%'
OR msg_content like '%充值%'
OR msg_content like '%承担%'
OR msg_content like '%成功%'
OR msg_content like '%车贷%'
OR msg_content like '%超期%'
OR msg_content like '%偿清%'
OR msg_content like '%偿还%'
OR msg_content like '%财富%'
OR msg_content like '%避免%'
OR msg_content like '%本息%'
OR msg_content like '%本期%'
OR msg_content like '%本金%'
OR msg_content like '%被执行人%'
OR msg_content like '%保证%'
OR msg_content like '%绑定%'
OR msg_content like '%按时%'
OR msg_content like '%逾 期%'
OR msg_content like '%追偿%'
OR msg_content like '%到账%'
OR msg_content like '%绑卡%'
OR msg_content like '%应还%'
OR msg_content like '%申请已通过%'
OR msg_content like '%代还%'
OR msg_content like '%需还%'
OR msg_content like '%要还%'
OR msg_content like '%成功还%'
OR msg_content like '%已还%'
OR msg_content like '%要到期%'
